﻿namespace Common
{
    using System;
    using System.Data;
    using System.IO;
    using System.Text;
    using System.Web;
    using NPOI.HSSF.UserModel;
    using NPOI.SS.UserModel;

    public class ExcelHelper
    {


        /// <summary>
        /// datatable导出到excel
        /// </summary>
        /// <param name="dtSource">datatable</param>
        /// <param name="strFileName">strFileName</param>
        /// <param name="sup">Supplier</param>
        /// <returns></returns>
        public  void DtToExcel(DataTable dtSource, string FileName)
        {
           
            try
            {
                HSSFWorkbook workbook = new HSSFWorkbook();
                //HSSFSheet sheet = workbook.CreateSheet();
                ISheet sheet = workbook.CreateSheet();
                //填充表头
                //HSSFRow dataRow = sheet.CreateRow(0);
                IRow dataRow = sheet.CreateRow(0);
                foreach (DataColumn column in dtSource.Columns)
                {
                    dataRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
                }

                //填充内容
                for (int i = 0; i < dtSource.Rows.Count; i++)
                {
                    dataRow = sheet.CreateRow(i + 1);
                    for (int j = 0; j < dtSource.Columns.Count; j++)
                    {
                        dataRow.CreateCell(j).SetCellValue(dtSource.Rows[i][j].ToString());
                    }
                }
                HttpContext curContext = HttpContext.Current;
                FileName = FileName + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xls";
                string filePath = curContext.Server.MapPath("\\down\\" + FileName);
                //保存
               
                FileStream fs = File.OpenWrite(filePath);
                workbook.Write(fs);//向打开的这个Excel文件中写入表单并保存。  
                workbook.Dispose();
                fs.Close();

                //下载
                fs = File.OpenRead(filePath);
                BinaryReader r = new BinaryReader(fs);
                r.BaseStream.Seek(0, SeekOrigin.Begin);    //将文件指针设置到文件开 
                byte[] bs = r.ReadBytes((int)r.BaseStream.Length);

                fs.Close();               

                // 设置编码和附件格式
                curContext.Response.ContentType = "application/vnd.ms-excel";
                curContext.Response.ContentEncoding = Encoding.UTF8;
                curContext.Response.Charset = "";
                curContext.Response.AppendHeader("Content-Disposition",
                "attachment;filename=" + HttpUtility.UrlEncode(FileName, Encoding.UTF8));

                
                curContext.Response.BinaryWrite(bs);
                curContext.Response.End();
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
          
        }


        ///// <summary>
        ///// 合并单元格
        ///// </summary>
        ///// <param name="sheet">要合并单元格所在的sheet</param>
        ///// <param name="rowstart">开始行的索引</param>
        ///// <param name="rowend">结束行的索引</param>
        ///// <param name="colstart">开始列的索引</param>
        ///// <param name="colend">结束列的索引</param>
        //public void SetCellRangeAddress(ISheet sheet, int rowstart, int rowend, int colstart, int colend)
        //{
        //    CellRangeAddress cellRangeAddress = new CellRangeAddress(rowstart, rowend, colstart, colend);
        //    sheet.AddMergedRegion(cellRangeAddress);
        //}


       


        //////////  现用导出  \\\\\\\\\\  
        /// <summary>
        /// 用于Web导出                                                                                             第一步
        /// </summary>
        /// <param name="dtSource">源DataTable</param>
        /// <param name="strHeaderText">表头文本</param>
        /// <param name="strFileName">文件名</param>
        public void ExportByWeb(DataTable dtSource, string strHeaderText, string strFileName)
        {
            HttpContext curContext = HttpContext.Current;

            // 设置编码和附件格式
            curContext.Response.ContentType = "application/vnd.ms-excel";
            curContext.Response.ContentEncoding = Encoding.UTF8;
            curContext.Response.Charset = "";
            curContext.Response.AppendHeader("Content-Disposition",
            "attachment;filename=" + HttpUtility.UrlEncode(strFileName, Encoding.UTF8));

            //curContext.Response.BinaryWrite(Export(dtSource, strHeaderText).GetBuffer());
            curContext.Response.End();
        }


        
    }
}
